package com.rhit.rosehack.SQLObjects;

import java.sql.*;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Properties;

import com.rhit.rosehack.engine.Player;
import com.rhit.rosehack.engine.Mob;

public class Query {
	private static Connection DatabaseConnection = null;

	public static Connection getConnection() {
		checkConnection();
		return DatabaseConnection;
	}

	// Checks Connection
	private static void checkConnection() {
		boolean error = false;

		if (DatabaseConnection == null) {
			Properties prop = new Properties();
			prop.setProperty("username", "RoseHack_user");
			prop.setProperty("password", "RoseHack_user");

			try {
				DatabaseConnection = DriverManager.getConnection(
						"jdbc:sqlserver://whale.csse.rose-hulman.edu", prop);
			} catch (SQLException e) {
				e.printStackTrace();
				error = true;
			} finally {
				if (DatabaseConnection == null || error) {
					System.out
							.println("Error opening database. Shutting down.");
					System.exit(-1);
				}
			}
		}
	}

	// Get's PLAYER given NAME
	public SQLObject getPlayer(String playerName) {
		ArrayList<SQLObject> array = new ArrayList<SQLObject>();

		String query = "EXEC rh_get_user '" + playerName + "'";

		try {
			checkConnection();

			Statement s = DatabaseConnection.createStatement();
			ResultSet rs = s.executeQuery(query);

			while (rs.next()) {
				SQLObject obj = resultSetToObject(rs, SQL_Type.Player_Snapshot);

				if (obj == null) {
					System.out.println("Error creating object from query...");
				} else {
					array.add(obj);
				}
			}

			s.close();
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("Error querying the database....");
			System.exit(-1);
		}
		if (array.size() == 1)
			return array.get(0);
		else
			return null;
	}

	// UPDATE CREATURE LOCATION ON DATABASE
	public void updateLocation(int creatureID, int chunkID, int x, int y) {
		checkConnection();

		try {
			DatabaseConnection.createStatement().executeUpdate(
					"EXEC rh_set_creature_location " + creatureID + ", "
							+ chunkID + ", " + x + ", " + y);
		} catch (SQLException e) {
			System.out.println("Unable to update Is_On");
			e.printStackTrace();
			System.exit(-1);
		}
	}

	// UPDATES NUMBER OF FLOORS
	public void updateFloors(int worldID, int floors) {
		checkConnection();

		try {
			DatabaseConnection.createStatement().executeUpdate(
					"EXEC rh_set_world_floors " + worldID + ", " + floors);
		} catch (SQLException e) {
			System.out.println("Unable to update Is_On");
			e.printStackTrace();
			System.exit(-1);
		}
	}

	// DELETES WORLD
	public void delete(String name) {
		checkConnection();

		try {
			int worldID = this.getWorld(name);
			DatabaseConnection.createStatement().executeUpdate(
					"EXEC rh_delete_world " + worldID);
		} catch (SQLException e) {
			e.printStackTrace();
			System.exit(-1);
		}
	}

	// GETS LIST OF PLAYERS
	public ArrayList<Player> getPlayers() {
		ArrayList<Player> list = new ArrayList<Player>();
		try {
			checkConnection();
			PreparedStatement s = DatabaseConnection
					.prepareStatement("EXEC rh_get_user");
			ResultSet rs = null;

			s.executeQuery();
			rs = s.getResultSet();

			while (rs.next()) {
				list.add((Player) resultSetToObject(rs,
						SQL_Type.Player_Snapshot));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			System.exit(-1);
		}

		return list;
	}

	// GETS ALL MOBS ON A CHUNK
	public ArrayList<Mob> getMobsForChunk(int chunk_id) {
		ArrayList<Mob> list = new ArrayList<Mob>();
		try {
			checkConnection();
			PreparedStatement s = DatabaseConnection
					.prepareStatement("EXEC rh_get_mobs ?");
			s.setInt(1, chunk_id);
			ResultSet rs = null;

			s.executeQuery();
			rs = s.getResultSet();

			while (rs.next()) {
				list.add((Mob) resultSetToObject(rs, SQL_Type.Mob_Snapshot));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			System.exit(-1);
		}

		return list;
	}

	public Collection<Item> getItemsForChunk(int chunk_id) {
		ArrayList<Item> list = new ArrayList<Item>();
		try {
			checkConnection();
			PreparedStatement s = DatabaseConnection
					.prepareStatement("EXEC rh_get_items ?");
			s.setInt(1, chunk_id);
			ResultSet rs = null;

			s.executeQuery();
			rs = s.getResultSet();

			while (rs.next()) {
				list.add((Item) resultSetToObject(rs, SQL_Type.Item_Snapshot));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			System.exit(-1);
		}

		return list;
	}

	private SQLObject resultSetToObject(ResultSet rs, SQL_Type type)
			throws SQLException {
		SQLObject ret = null;

		if (rs == null) {
			return null;
		}

		switch (type) {
		case Player_Snapshot:
		case Mob_Snapshot: {
			Creature c;

			if ((rs.getInt("Creature_Type") == 0))
				ret = new Player();
			else
				ret = new Mob();

			c = (Creature) ret;

			c.setCreatureID(rs.getInt("Creature_ID"));
			c.setName(rs.getString("Creature_Name"));
			c.setLevel(rs.getInt("Creature_Level"));
			c.setHP(rs.getInt("Creature_HP"));
			c.setMP(rs.getInt("Creature_MP"));
			c.setAttack(rs.getInt("Creature_Attack"));
			c.setDefense(rs.getInt("Creature_Defense"));
			c.setMagic(rs.getInt("Creature_Magic"));
			c.setType(rs.getInt("Creature_Type"));

			c.setX(rs.getInt("X_Location"));
			c.setY(rs.getInt("Y_Location"));

		}
			break;

		case Item:
		case Item_Snapshot: {
			Item i;
			ret = new Item();
			i = (Item) ret;

			i.setID(rs.getInt("Item_ID"));
			i.setName(rs.getString("Item_Name"));
			i.setStats(rs.getLong("Item_Stats"));
			if (type == SQL_Type.Item_Snapshot) {
				i.setX(rs.getInt("Item_X"));
				i.setY(rs.getInt("Item_Y"));
			} else {
				i.setX(-1);
				i.setY(-1);
			}
		}
			break;

		case World: {
			World w;

			ret = new World();
			w = (World) ret;

			w.setID(rs.getInt("World_ID"));
			w.setName(rs.getString("World_Name"));
			w.setFloor_count(rs.getInt("Floor_Count"));

		}
			break;

		case Chunk: {
			Chunk c;

			ret = new Chunk();
			c = (Chunk) ret;

			c.setID(rs.getInt("Chunk_ID"));
			c.setFile(rs.getString("Chunk_File"));
			c.setType(rs.getInt("Chunk_Type"));
			c.setWorld_id(rs.getInt("World_ID"));

		}
			break;

		case Is_On: {
			Is_On i;

			ret = new Is_On();
			i = (Is_On) ret;

			i.setChunk_id(rs.getInt("Chunk_ID"));
			i.setCreature_id(rs.getInt("Creature_ID"));
			i.setX_location(rs.getInt("X_Location"));
			i.setY_location(rs.getInt("Y_Location"));

		}
			break;

		}

		return ret;
	}

	public int getFloor(String playerName) {

		String query = "{call rh_get_chunk(?,?)}";

		try {
			checkConnection();

			CallableStatement s = DatabaseConnection.prepareCall(query);
			s.setString(1, playerName);
			s.registerOutParameter(2, Types.INTEGER);
			s.executeUpdate();

			int temp = s.getInt(2);

			s.close();
			return temp;
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("Error querying the database....");
			System.exit(-1);
		}
		return 0;
	}

	// Gets the WORLD ID of playerNAME
	public int getWorld(String playerName) {

		String query = "{call rh_get_world(?,?)}";

		try {
			checkConnection();

			CallableStatement s = DatabaseConnection.prepareCall(query);
			s.setString(1, playerName);
			s.registerOutParameter(2, Types.INTEGER);
			s.executeUpdate();

			int temp = s.getInt(2);

			s.close();
			return temp;
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("Error querying the database....");
			System.exit(-1);
		}
		return 0;
	}

	// GETS NUMBER OF FLOORS OF playerNAME
	public int getFloors(String playerName) {

		String query = "{call rh_get_world_floors(?,?)}";

		try {
			checkConnection();

			CallableStatement s = DatabaseConnection.prepareCall(query);
			s.setInt(1, this.getWorld(playerName));
			s.registerOutParameter(2, Types.INTEGER);
			s.executeUpdate();

			int temp = s.getInt(2);

			s.close();
			return temp;
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("Error querying the database....");
			System.exit(-1);
		}
		return 0;
	}

	// GETS NEXT CHUNK ID FROM THIS ID
	public int getNext(int chunkID) {

		String query = "{call rh_get_next_chunk(?,?)}";

		try {
			checkConnection();

			CallableStatement s = DatabaseConnection.prepareCall(query);
			s.setInt(1, chunkID);
			s.registerOutParameter(2, Types.INTEGER);
			s.executeUpdate();

			int temp = s.getInt(2);

			s.close();
			return temp;
		} catch (SQLException e) {

			// e.printStackTrace();
			// System.out.println( "Error querying the database...." );
			return -1;
		}
	}

	// GETS PREV CHUNK ID FROM THIS ID
	public int getPrev(int chunkID) {

		String query = "{call rh_get_prev_chunk(?,?)}";

		try {
			checkConnection();

			CallableStatement s = DatabaseConnection.prepareCall(query);
			s.setInt(1, chunkID);
			s.registerOutParameter(2, Types.INTEGER);
			s.executeUpdate();

			int temp = s.getInt(2);

			s.close();
			return temp;
		} catch (SQLException e) {

			// e.printStackTrace();
			// System.out.println( "Error querying the database...." );
			return -1;
		}
	}

	// UPDATES A CREATURES STATS
	public void updateCreature(int creatureID, int level, int hp, int mp,
			int attack, int def, int mag) {

		String query = "{call rh_update_stats(?,?,?,?,?,?,?)}";

		try {
			checkConnection();

			CallableStatement s = DatabaseConnection.prepareCall(query);
			s.setInt(1, creatureID);
			s.setInt(2, level);
			s.setInt(3, hp);
			s.setInt(4, mp);
			s.setInt(5, attack);
			s.setInt(6, def);
			s.setInt(7, mag);
			s.executeUpdate();

			s.close();

		} catch (SQLException e) {

			// e.printStackTrace();
			// System.out.println( "Error querying the database...." );
		}
	}

	// DELETES A CREATURE
	public void deleteCreature(int creatureID) {

		String query = "{call rh_delete_creature(?)}";

		try {
			checkConnection();

			CallableStatement s = DatabaseConnection.prepareCall(query);
			s.setInt(1, creatureID);
			s.executeUpdate();

			s.close();

		} catch (SQLException e) {

			// e.printStackTrace();
			// System.out.println( "Error querying the database...." );
		}
	}

	// gen a floor, add to Database
	public int genFloor(String playerName, int worldID, int prevChunk, int floor) {
		String query = "{call rh_gen_chunk(?,?,?,?,?,?)}";

		try {
			checkConnection();

			CallableStatement s = DatabaseConnection.prepareCall(query);
			s.setString(1, playerName);
			s.setInt(2, worldID);
			s.setInt(3, prevChunk);
			s.setInt(4, floor);
			s.registerOutParameter(5, Types.INTEGER);
			s.registerOutParameter(6, Types.VARCHAR);
			s.executeUpdate();

			int temp = s.getInt(5);

			s.close();
			return temp;
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("Error querying the database....");
			System.exit(-1);
		}
		return 0;

	}

	// ADDS CREATURE TO DATABASE
	public int genCreature(String name, int level, int hp, int mp, int attack,
			int defense, int magic, int type, int x, int y, int chunkID) {

		String query = "{call rh_gen_mob(?,?,?,?,?,?,?,?,?,?,?,?)}";

		try {
			checkConnection();

			CallableStatement s = DatabaseConnection.prepareCall(query);
			s.setString(1, name);
			s.setInt(2, level);
			s.setInt(3, hp);
			s.setInt(4, mp);
			s.setInt(5, attack);
			s.setInt(6, defense);
			s.setInt(7, magic);
			s.setInt(8, type);
			s.setInt(9, chunkID);
			s.setInt(10, x);
			s.setInt(11, y);

			s.registerOutParameter(12, Types.INTEGER);
			s.executeUpdate();

			int temp = s.getInt(12);

			s.close();
			return temp;
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("Error querying the database....");
			System.exit(-1);
		}
		return 0;

	}

	public int genItem(int id, int x, int y, Item item) {
		try {
			int out = 0;
			checkConnection();
			CallableStatement s = DatabaseConnection
					.prepareCall("{call rh_gen_item( ?, ?, ?, ?, ? )}");

			s.setInt(1, id);
			s.setInt(2, x);
			s.setInt(3, y);
			s.setLong(4, item.getStats());
			s.registerOutParameter(5, Types.INTEGER);

			s.executeUpdate();
			out = s.getInt( 5 );
			s.close();
			
			return out;
		} catch (SQLException e) {
			e.printStackTrace();
			System.exit(-1);
		}

		return 0;
	}

	public void pickupItem(int item_id, int creature_id) {

		try {
			checkConnection();
			CallableStatement s = DatabaseConnection
					.prepareCall("{call rh_pickup_item( ?, ? )}");

			s.setInt(1, item_id);
			s.setInt(2, creature_id);

			s.executeUpdate();
			s.close();
		} catch (SQLException e) {
			e.printStackTrace();
			System.exit(-1);
		}
	}

	public Collection<Item> getCreatureItems(int craeture_id) {
		ArrayList<Item> list = new ArrayList<Item>();
		try {
			checkConnection();
			PreparedStatement s = DatabaseConnection
					.prepareStatement("EXEC rh_get_creature_items ?");
			s.setInt(1, craeture_id);
			ResultSet rs = null;

			s.executeQuery();
			rs = s.getResultSet();

			while (rs.next()) {
				list.add((Item) resultSetToObject(rs, SQL_Type.Item));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			System.exit(-1);
		}

		return list;
	}
}
